Unique Primary Index

A Unique Primary Index (UPI) is unique and can‘t have any duplicates. A Unique Primary Index means that the values for the selected column must be unique. If you try and insert a row with a Primary Index value that is already in the table, the row will be rejected. An UPI enforces UNIQUENESS for a column.

• A Unique Primary Index will always spread the table rows evenly amongst the AMPs.
• A Unique Primary Index  always one AMP Operation.
• In given figure we have selected EMP_NO to be our Primary Index. Because we have designated EMP_NO to be a Unique Primary Index, there can be no duplicate employee numbers in the table. 
• While accessing the record there is not required to distribute the file into spool Space.

CREATE TABLE EMP
(
Emp_no  Varchar(10),
Dept_no  Integer,
First_name Varchar(20),
Last_name Varchar(20),
Salary Decimal(10,2)
)
UNIQUE PRIMARY INDEX (Emp_no)

Every table must have one and only one Primary Index. Because Teradata distributes the data based on the Primary Index columns value it is quite obvious that you must have a primary index and that there can be only one primary index per table. Teradata uses the Primary Index and NOT the Primary Key. There are two reasons you might pick a different Primary Index then your Primary Key.

• For Performance reasons
• Known access paths.

PRIMARY INDEX is mandatory for any table in Teradata. While creating a table in Teradata, Teradata will look for any primary index specified in the DDL. If the index is not specified, secondly it will look for Primary key constraint in DDL for making it primary index.If the above conditions are not met then Teradata will take the first column as a Primary index.It is recommended that we should explicitly specify the PRIMARY INDEX for a column in a table since we can assign a proper field for a primary index that will help Teradata to spread the data across the amps efficiently.